"""
Case Type   : openGauss 支持忽略PLSQL间的依赖进行创建
Case Name   : 存储过程的入参是自定义type，重命名类型名称后重新调用，部分测试点合理报错
Create At   : 2023.11.15
Owner       : zhanghuan96
Description :
    1. 使用gs_guc reload方式设置参数为plpgsql_dependency
    2. 创建复合类型
    3. 创建存储过程
    4. 调用存储过程
    5. 重命名type名称
    6. 删除存储过程
    7. 重命名type名称
    8.创建存储过程
    9.调用存储过程
    10. 清理环境
Expect      :
    1. 成功
    2. 成功
    3. 成功
    4. 成功
    5. 报错，存在依赖无法修改
    6. 成功
    7. 成功
    8.成功
    9.成功
    10.成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class PlpgsqlDependency(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.common = Common()
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.type_name = "type_guc0010"
        self.proc_name = "proc_guc0010"
        self.default_value = self.common.show_param('behavior_compat_options')
        self.expect = 'ERROR:  The rename operator of type_guc0010 is not ' \
                      'allowed, because it is referenced by the other object'

    def test_guc(self):
        text = '--step1:使用gs_guc reload方式设置参数为plpgsql_dependency;' \
               'expect:成功--'
        self.log.info(text)
        mod_msg = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            "behavior_compat_options "
                                            "='plpgsql_dependency'")
        self.log.info(mod_msg)
        self.assertTrue(mod_msg)
        sql_cmd = "show behavior_compat_options;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual('plpgsql_dependency',
                         sql_res.splitlines()[-2].strip(),
                         '查询失败')

        text = '--step2:创建复合类型;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''drop type if exists {self.type_name};
        create type {self.type_name} as (
            id integer,
            name varchar,
            addr text
        );'''
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('CREATE TYPE', sql_res, '查询失败')

        text = '--step3:创建存储过程;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''create or replace procedure \
        {self.proc_name}(a {self.type_name})
        is
        begin
            RAISE INFO 'call a: %', a;
        end;
        '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('CREATE PROCEDURE', sql_res, '查询失败')

        text = '--step4:调用存储过程;expect:成功--'
        self.log.info(text)
        sql_cmd = f"call {self.proc_name}((1,'zhang','shanghai'));"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('INFO:  call a: (1,zhang,shanghai)', sql_res, '查询失败')

        text = '--step5:重命名type名称;expect:报错，存在依赖无法修改--'
        self.log.info(text)
        sql_cmd = f"alter type {self.type_name} rename to " \
                  f"{self.type_name}_bak;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.expect, sql_res, '查询失败')

        text = '--step6:删除存储过程;expect:成功--'
        self.log.info(text)
        sql_cmd = f"drop procedure {self.proc_name};"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('DROP PROCEDURE', sql_res, '查询失败')

        text = '--step7:重命名type名称;expect:成功--'
        self.log.info(text)
        sql_cmd = f"alter type {self.type_name} rename to " \
                  f"{self.type_name}_bak;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('ALTER TYPE', sql_res, '查询失败')

        text = '--step8:创建存储过程;expect:成功--'
        self.log.info(text)
        sql_cmd = f'''create or replace procedure \
                {self.proc_name}_01(a {self.type_name}_bak)
                is
                begin
                    RAISE INFO 'call a: %', a;
                end;
                '''
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('CREATE PROCEDURE', sql_res, '查询失败')

        text = '--step9:调用存储过程;expect:成功--'
        self.log.info(text)
        sql_cmd = f"call {self.proc_name}_01((1,'zhang','shanghai'));"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn('INFO:  call a: (1,zhang,shanghai)', sql_res, '查询失败')

    def tearDown(self):
        text = '----step10:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop procedure {self.proc_name}_01;" \
                  f"drop type if exists {self.type_name}_bak;"
        self.log.info(sql_cmd)
        sql_res = self.pri_sh.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        mod_msg = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f"behavior_compat_options "
                                            f"='{self.default_value}'")
        self.log.info(mod_msg)
        self.assertTrue(mod_msg)
        self.assertIn('DROP PROCEDURE', sql_res, '删除失败')
        self.assertIn('DROP TYPE', sql_res, '删除失败')
        self.log.info(f"-----{os.path.basename(__file__)} end-----")
